package zhou.contest10;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import jin.competition.jan11.User;
import zhou.teamwork10.BaseDao;

public class StudentsController {
	
	//---------计算作业优秀率--------
	public boolean excellentRate(Students s){
		
		BaseDao dao=new BaseDao();
		Connection con=dao.getCon();				
    	String sql1="select * from students";   	
    	String sql2="select * from homework where score >=90";
    	String sql3="select * from homework where score =0";
    	   	
    	int num1=0;
    	int num2=0;
    	  try{		
    	  PreparedStatement ps1=con.prepareStatement(sql1);   	 
    	  ResultSet rs1=ps1.executeQuery();
    	  PreparedStatement ps2=con.prepareStatement(sql2);   	 
    	  ResultSet rs2=ps2.executeQuery();
    	  PreparedStatement ps3=con.prepareStatement(sql3);   	 
    	  ResultSet rs3=ps3.executeQuery();
    	  
    	  while(rs1.next()){ 
    		  num1++;
    		  System.out.println(rs1.getString("schoolID")+"\t"+rs1.getString("name"));	   		  
   	  }
    	  num1++;
    	  System.out.println("学生人数："+num1);
    	  
    	  while(rs2.next()){   		 
    		 // System.out.println(rs2.getString("name")+"\t"+rs2.getString("score"));	
    		  num2++;
   	  }
    	  System.out.println("优秀学生人数："+num2); 
    	  System.out.println("作业优秀率： "+(double)num2/num1); 
    	
    	  while(rs3.next()){    		 
    		  System.out.println("没交作业的学生："+rs3.getString("name"));	   		  
   	  }
    	  
   	  if(rs1!=null){
    		  rs1.close();
    	  }
   	  if(ps1!=null){
    		ps1.close();    		   		
    	  }
   	  
   	if(rs2!=null){
		  rs2.close();
	  }
	  if(ps2!=null){
		ps2.close();    		   		
	  }
	  if(rs3!=null){
		  rs3.close();
	  }
	  if(ps3!=null){
		ps3.close();    		   		
	  }
	  
    	  if(con!=null){
    		con.close();    		   		
   	  }		
    			
          }catch(SQLException e){
    	     e.printStackTrace();
    	}
    	return true;
    }
    

	

}
